package tp1.dao;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import tp1.models.Cliente;
import tp1.DbConnection;
import tp1.models.Chamada;
import tp1.models.Linha;
import tp1.models.Plano;

/**
 * Implementação DAO usando JDBC
 * @author tulio
 */
public class ChamadaDao {

    private static String mainSql = "SELECT * FROM CHAMADA " +
        "INNER JOIN LINHA L1 ON CHAMADA.ORIGEM = L1.NUMERO " +
        "INNER JOIN CLIENTE C1 ON L1.CLIENTE = C1.CPF " +
        "INNER JOIN PLANO P1 ON L1.PLANO = P1.CODIGO " +
        "LEFT JOIN LINHA L2 ON CHAMADA.DESTINO = L2.NUMERO " +
        "LEFT JOIN CLIENTE C2 ON L2.CLIENTE = C2.CPF " +
        "LEFT JOIN PLANO P2 ON L2.PLANO = P2.CODIGO";

    public static int delete(String id) throws SQLException {
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate("DELETE FROM CHAMADA WHERE ID = '" + id + "'");
    }

    public static Chamada find(int id) throws SQLException, Exception {
        Chamada c = null;
        String sql;
        sql = mainSql + " WHERE ID = " + id ;
        
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        if(rs.next()) {
            c = getChamadaByResultSet(rs);
        }
        return c;
    }

    public static ArrayList<Chamada> list() throws SQLException, Exception {
        Cliente c1, c2;
        Plano p1, p2;
        Linha origem, destino;
        Chamada c;
        ArrayList<Chamada> list = new ArrayList();
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery(mainSql);

        while(rs.next()){
            list.add(getChamadaByResultSet(rs));
        }
        
        return list;
    }

    public static int insert(Chamada chamada) throws SQLException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        String insert = "INSERT INTO CHAMADA (DESTINO, ORIGEM, INICIO, FIM) VALUES('" +
            chamada.getDestino().getNumero() + "','" + 
            chamada.getOrigem().getNumero() + "','" +
            sdf.format(chamada.getInicio()) + "','" +
            sdf.format(chamada.getFim()) + "')";
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate(insert);
    }

    public static int update(Chamada chamada) throws SQLException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        String update = "UPDATE CHAMADA SET DESTINO = '" + chamada.getDestino().getNumero() +
            "', ORIGEM = '" + chamada.getOrigem().getNumero() +
            "', INICIO = '" + sdf.format(chamada.getInicio()) +
            "', FIM = '" + sdf.format(chamada.getFim()) +
            "' WHERE ID = '" + chamada.getId() + "'";
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        return stmt.executeUpdate(update);
    }

    public static int save(Chamada chamada) throws SQLException, Exception {
        if(find(chamada.getId()) == null){
            return insert(chamada);
        } else {
            return update(chamada);
        }
    }

    public static Chamada getChamadaByResultSet(ResultSet rs) throws SQLException, Exception{
        Cliente c1 = ClienteDao.getClienteByResultSet(rs, "C1");
        Plano p1 = PlanoDao.getPlanoByResultSet(rs, "P1");

        Linha origem = new Linha(rs.getString("CHAMADA.ORIGEM"));
        origem.setDataDeAtivacao(rs.getTimestamp("L1.DATA_DE_ATIVACAO"));
        if(rs.getTimestamp("L1.DATA_DE_CANCELAMENTO") != null)
            origem.setDataDeCancelamento(rs.getTimestamp("L1.DATA_DE_CANCELAMENTO"));
        origem.setDiaDePagamento(rs.getInt("L1.DIA_DE_PAGAMENTO"));
        origem.setCliente(c1);
        origem.setPlano(p1);

        Cliente c2 = ClienteDao.getClienteByResultSet(rs, "C2");
        Plano p2 = PlanoDao.getPlanoByResultSet(rs, "P2");

        Linha destino = new Linha(rs.getString("CHAMADA.DESTINO"));
        if(rs.getString("L2.NUMERO") != null){
            destino.setDataDeAtivacao(rs.getTimestamp("L2.DATA_DE_ATIVACAO"));
            destino.setDataDeCancelamento(rs.getTimestamp("L2.DATA_DE_CANCELAMENTO"));
            destino.setDiaDePagamento(rs.getInt("L2.DIA_DE_PAGAMENTO"));
            destino.setCliente(c2);
            destino.setPlano(p2);
        }

        Chamada c = new Chamada();
        c.setId(rs.getInt("CHAMADA.ID"));
        c.setOrigem(origem);
        c.setDestino(destino);
        c.setInicio(rs.getTimestamp("CHAMADA.INICIO"));
        c.setFim(rs.getTimestamp("CHAMADA.FIM"));
        return c;
    }


    public static ArrayList<Chamada> findByTelefoneEMes(String numero, Calendar mes) throws SQLException, Exception {
        ArrayList<Chamada> c = new ArrayList();

        Calendar cInicio = new GregorianCalendar();
        cInicio.set(Calendar.YEAR, mes.get(Calendar.YEAR));
        cInicio.set(Calendar.MONTH, mes.get(Calendar.MONTH)-1);
        cInicio.set(Calendar.DAY_OF_MONTH, 1);
        cInicio.set(Calendar.HOUR, 0);
        cInicio.set(Calendar.MINUTE, 0);
        cInicio.set(Calendar.SECOND, 0);
        Date inicio = cInicio.getTime();

        Calendar cFim = new GregorianCalendar();
        cFim.set(Calendar.YEAR, mes.get(Calendar.YEAR));
        cFim.set(Calendar.MONTH, mes.get(Calendar.MONTH)-1);
        cFim.set(Calendar.DAY_OF_MONTH, mes.getMaximum(Calendar.DAY_OF_MONTH));
        cFim.set(Calendar.HOUR, 23);
        cFim.set(Calendar.MINUTE, 59);
        cFim.set(Calendar.SECOND, 59);
        Date fim = cFim.getTime();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


        String sql;
        sql = mainSql + " WHERE L1.NUMERO = " + numero +
            " AND CHAMADA.INICIO BETWEEN '" + sdf.format(inicio) + "' AND '" + sdf.format(fim) + "'";

        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery(sql);        

        while(rs.next()) {
            c.add(getChamadaByResultSet(rs));
        }
        return c;
    }

    public static Date findUltimaChamadaByTelefone(String numero) throws SQLException, Exception {
        ArrayList<Chamada> c = new ArrayList();

        String sql =  "SELECT MAX(CHAMADA.FIM) AS FIM FROM CHAMADA " +
        "INNER JOIN LINHA L1 ON CHAMADA.ORIGEM = L1.NUMERO";
        sql = sql + " WHERE L1.NUMERO = " + numero;
        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        Date r = null;
        while(rs.next()) {
            r = rs.getTimestamp("FIM");
        }
        return r;
    }

    public static Object[][] listAsVector() throws SQLException, Exception{
        int row = 0;
        ArrayList<Chamada> chamadas = ChamadaDao.list();
        Object[][] rows;

        if(chamadas.isEmpty()){
            rows = null;
        } else {
            rows = new Object[chamadas.size()][5];
        }

        for (Chamada c : chamadas) {
            rows[row][0] = c.getId();
            rows[row][1] = c.getOrigem().getNumero();
            rows[row][2] = c.getDestino().getNumero();
            rows[row][3] = c.getInicio();
            rows[row][4] = c.getFim();
            row++;
        }
        return rows;
    }

    public static ArrayList<Chamada> findByOrigem(String numero) throws SQLException, Exception {
        Chamada c = null;
        String sql;
        sql = mainSql + " WHERE ORIGEM = " + numero ;

        Statement stmt = (Statement) DbConnection.getInstance().createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        ArrayList<Chamada> list = new ArrayList();

        while(rs.next()) {
            list.add(getChamadaByResultSet(rs));
        }
        return list;
    }

    public static Object[][] findAsVector(String numero) throws SQLException, Exception{
        int row = 0;
        ArrayList<Chamada> chamadas = ChamadaDao.findByOrigem(numero);
        Object[][] rows;

        if(chamadas.isEmpty()){
            rows = null;
        } else {
            rows = new Object[chamadas.size()][5];
        }

        for (Chamada c : chamadas) {
            rows[row][0] = c.getId();
            rows[row][1] = c.getOrigem().getNumero();
            rows[row][2] = c.getDestino().getNumero();
            rows[row][3] = c.getInicio();
            rows[row][4] = c.getFim();
            row++;
        }
        return rows;
    }
    
}
